#!/usr/bin python
# -*- coding: utf-8 -*-
# @Time    : 2023/2/28 13:44
# @Author  : hhr
from django.db import connection


class search_data():
    def search_seal(self, days=0):
        """查询用印审批的数据"""
        if days:
            select_month = f"select DATE_FORMAT(a.create_time,'%c') yf, COUNT(*) cou from personal_center_sealapply a inner JOIN tb_users_message b ON a.apply_user_id=b.id inner join administrative_company c on b.company_id = c.id where state='five' AND a.create_time>='{days}' group by yf"
            select_company = f"select c.`name` bm, COUNT(*) cou from personal_center_sealapply a inner JOIN tb_users_message b ON a.apply_user_id=b.id inner join administrative_company c on b.company_id = c.id where state='five' AND a.create_time>='{days}' group by bm"
        else:
            select_month = "select DATE_FORMAT(a.create_time,'%c') yf, COUNT(*) cou from personal_center_sealapply a inner JOIN tb_users_message b ON a.apply_user_id=b.id inner join administrative_company c on b.company_id = c.id where state='five' AND YEAR(a.create_time) = YEAR(now()) group by yf"
            select_company = "select c.`name` bm, COUNT(*) cou from personal_center_sealapply a inner JOIN tb_users_message b ON a.apply_user_id=b.id inner join administrative_company c on b.company_id = c.id where state='five' AND YEAR(a.create_time) = YEAR(now()) group by bm"
        yf_list, data_list = [], []
        with connection.cursor() as cursor:
            cursor.execute(select_month)
            all_data = cursor.fetchall()
            for i in all_data:
                yf_list.append(str(i[0]))
                data_list.append(i[1])

        with connection.cursor() as cursor:
            print(select_company)
            cursor.execute(select_company)
            all_data2 = cursor.fetchall()
            dep_list, dep_number_list = [], []
            for tt in all_data2:
                dep_list.append(tt[0])
                dep_number_list.append(tt[1])

        return yf_list, data_list, dep_list, dep_number_list